---
title: SQL 优化
---

## 调优的目的
数据库优化的目的就是要让数据库运行的更快，也就是说响应时间更快，吞吐量更大。

一般情况下，通过以下方式确定调优的目标。

1. 用户的反馈
2. 日志分析。通过数据库日志和操作系统日志等方式找出异常情况，通过它们来定位遇到的问题。
3. 服务器资源使用监控
4. 数据库内部状况监控。通过活动会话（Active Session） 可以清楚了解数据库当前是否处于非常繁忙的状态，是否存在 SQL 堆积等。还可以对事务、锁等待等进行监控。


## 调优方法
需要调优的对象是整个数据库管理系统，它不仅包括 SQL 查询，还包括数据库的部署配置、架构等。从这个角度讲，我们思考的维度就不仅仅局限在 SQL 优化上了。

### 第一步，选择适合的 DBMS
在进行 SQL 调优之前，先选择 DBMS 。
### 第二步，优化表设计
选择了 DBMS 之后，就需要进行表设计了。RDBMS 中，每个对象都可以定义为一张表，表与表之间的关系代表了对象之间的关系。如果用的是 MySQL ,我们还可以根据不同表的使用需求，**选择不同的存储引擎**。

1. 表结构的设计尽量遵循第三范式的原则。
2. 如果分析查询应用比较多，尤其是需要进行多表联查的时候，可以采用反范式设计进行优化。反范式采用空间换时间的方式，通过增加冗余字段提高查询的效率。
3. 表字段的数据类型选择，关系到了查询效率的高度以及存储空间的大小。一般来说，如果字段可以采用数值类型就不要采用字符类型；字符长度要尽可能设计的短一些。针对字符类型来说，当确定字符长度固定时，就可以采用 CHAR 类型；当长度不固定时，通常采用 VARCHAR 类型。

### 第三步，优化逻辑查询
当建好数据表之后，首先要考虑的是逻辑查询优化。

SQL 查询优化，可以分为逻辑查询优化和物理查询优化。逻辑查询优化就是通过改变 SQL 语句的内容让 SQL 的执行效率更高效，采用的方式就是对 SQL 语句进行等价变换，对查询进行重写。

SQL 的查询优化包括了子查询优化、等价谓词重写、条件简化、连接消除和嵌套连接消除等。

:::tip
比如使用 EXISTS 子查询和 IN 子查询的时候，会根据小表驱动大表的原则选择适合的子查询。在 WHERE 子句中会尽量避免对字段进行函数运算，它们会让字段的索引失效。
:::
### 第四步、优化物理查询
物理查询优化是将逻辑查询的内容变成可以被执行的物理操作符，从而为后续执行器的执行提供准备。它的核心是高效地建立索引，并通过这些索引来做各种优化。

创建索引需要考虑的条件：
1. 如果数据重复度高，就不需要创建索引。通常在重复度超过 10% 的情况下，可以不创建这个索引。比如性别字段。
2. 要注意索引位置对索引使用的影响。比如在 WHERE 子句中对索引字段进行了表达式的计算，会造成这个字段索引的失效。
3. 要注意联合索引对索引使用的影响。我们在创建联合索引的时候会对多个字段创建索引，这时索引的顺序就很重要了。比如我们呢对字段 x,y,z 创建了索引，那么顺序是(x,y,z)还是(z,y,x),在执行的时候就存在差别。
4. 要注意多个索引对索引使用的影响。索引不是越多越好，因为每个索引都需要存储空间，索引多也就意味着需要更多的存储空间。此外，过多的索引会导致优化器在进行评估的时候增加类筛选出索引的计算时间，影响评估的效率。

查询优化器在对 SQL 语句进行等价变换之后，还需要根据数据表的索引情况和数据情况确定访问路径，这就决定了执行 SQL 时所需要消耗的资源。SQL 查询时需要对不同的数据表进行查询，因此在物理查询优化阶段也需要确定这些查询所采用的路径，具体的情况包括：
1. 单表扫描：对于单表扫描来说，我们可以全表扫描所有的数据，也可以局部扫描。
2. 两张表的连接：常用的连接方式包括了嵌套循环连接、HASH连接和合并连接。
3. 多张表的连接：多张数据表进行连接的时候，顺序很重要，因为不同的连接路径查询的效率不同，搜索空间也会不同。我们在进行多表连接的时候，搜索空间可能会达到很高的数量等级，巨大的搜索空间显然会占据更多的资源，因此需要通过调整连接顺序，讲搜索空间调整在一个可接受的范围内。

物理查询优化是在确定了逻辑查询优化之后，采用物理优化技术（比如索引等），通过计算代价模型对各种可能的访问路径进行估算。从而找到执行方式中代价最小的作为执行计划。

优化物理查询的重点是索引的创建和使用。
### 第五步，使用 Redis 作为缓存
除了对 SQL 本身进行优化以外，还可以使用缓存提供查询的效率。

因为数据都是存放到数据库中，我们需要从数据库层中取出数据放到内存中进行业务逻辑的操作，当用户量增大的时候，如果频繁地进行数据查询，会消耗数据库的很多资源。如果将常用的数据直接放到内存中，就会大幅度提升查询的效率。


通常我们对于查询响应要求较高的场景（响应时间短，吞吐量大），可以考虑内存数据库。

### 第七步，库级优化
库级优化是站在数据库的维度上进行的优化策略，比如控制一个库中的数据表数量。另外还可以采用主从架构优化我们的读写策略。

如果读和写的业务量都很大，并且它们都在同一个数据库服务器中进行操作，那么数据库的性能就会出现瓶颈，这时为了提升系统的性能，优化用户体验，可以采用读写分离的方式降低主数据库的负载，比如用主数据库（master）完成写操作，用从数据库（slave）完成读操作。

除此之外，我们还可以对数据库分库分表。当数据量级达到亿级以上时，有时候需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。

如果数据库中的数据表过多，可以采用垂直分库的方式，将关联的数据表部署在一个数据库上。

如果数据表中的列过多，可以采用垂直分表的方式，将数据表分拆成多张，把经常一起使用的列放到同一张表中。

如果数据表中的数据达到了亿级以上，可以考虑水平切分，将大的数据表分拆成不同的子表，每张表保持相同的表结构。比如你可以按照年份来划分，把不同年份的数据放到不同的数据表中。2017年、2018年 和 2019 年的数据就可以分别放到三张数据表中。

采用垂直分表的形式，就是将一张数据表分拆成多张表，采用水平拆分的方式，就是将单张数据量大的表按照某个属性维度分成结构相同的小表(比如按年份拆分一份包含 30 年的数据为 30 份小表)

分拆在提升数据库性能的同时，也会增加维护和使用成本。


## 总结
在数据库调优中，我们的目标就是响应速度更快，吞吐量更大。利用宏观的监控工具和微观的日志分析可以帮助我们快速找到调优的思路和方式。

在 SQL 调优之前，首先先选择 DBMS 和数据表的设计方式。

另外，可以把 SQL 查询优化分成两个部分，逻辑查询优化和物理查询优化。

逻辑查询优化就是换一种SQL写法，物理查询优化则是通过索引和表连接方式等技术来进行优化。

还可以通过 Redis 来增强数据库的性能。

最后，通过对数据库进行垂直或者水平切分，突破单一数据库或数据表的访问限制，提升查询的性能。




import DocCardList from '@theme/DocCardList';


<DocCardList/>
